import pymysql

conn = pymysql.connect(host='127.0.0.1',port= 3306,user = 'root',passwd='22267721mq',)
cursor = conn.cursor()

find_databases = 'show databases'
use_databases = 'use {}'
find_tables = 'show tables'

create_table = 'create table {} (sid int auto_increment primary key ,s_name varchar(6) not NULL ,age int,sex varchar(3),address varchar(30),education varchar(10) not NULL ,phone varchar(11) not NULL ,class_ varchar(12),introducer varchar(6) default " ")'
add_data = 'insert into dahua (s_id,s_name,s_age,s_sex,s_address,education,phone,class_)values({})'
delete_data = 'delete from dahua where {} '
update_data = 'update dahua set {} where sid={}'
findall_data = 'select {} from dahua'

def show_database():
    cursor.execute(find_databases)
    res = cursor.fetchall()
    return res

def use_database(database = 'pythonclass'):
    cursor.execute(use_databases.format(database))
    return database

def show_tables():
    cursor.execute(find_tables)
    res = cursor.fetchall()
    return res

def create_tables(table_name='Bei Ke Da Hua'):
    cursor.execute(create_table.format(table_name))

def select_find():
    cursor.execute(findall_data.format('*'))
    res = cursor.fetchall()
    return res

def add_datas():
    sid = input('sid:')
    name = input('name:')
    age = input('age:')
    sex = input('sex:')
    adress = input('adress:')
    education = input('education:')
    phone = input('phone:')
    class_ = input('class:')
    cursor.execute(add_data.format(sid,name,age,sex,adress,education,phone,class_))

def delete_datas():
    cursor.execute(delete_data)

def alter_datas(update_data1):

    cursor.execute(update_data1)

def show():
    print('''   展示数据库指令:1
                使用数据库指令:2
             查看数据库中table:3
                    创建表指令:4
                  添加数据指令:5
                  删除数据指令:6
                Alter 数据指令:7
              查询所有数据指令:8
              根据姓名查询指令:9
    ''')

def main():
    show()
    code = input('请输入指令:')
    if code == '1':
        res = show_database()
        print(res)

    elif code == '2':
        res = use_database()
        print('正在使用{} 数据库进行操作'.format(res))

    elif code == '3':
        res = show_tables()
        print('数据库中有如下表格:\n{}'.format(res))

    elif code == '4':
        create_tables()
        print('建表操作成功')

    elif code == '5':
        print('请输入添加数据 :')
        add_datas()
        print('添加成功!')

    elif code == '6':
        delete_datas()


    elif code == '7':
        a = '{}={}'
        list1 = []
        list2 = []
        list2.append(a)

        str_sid = input('您要改哪个人的数据,请输入其学号:')
        sid = int(str_sid)

        number = input('请问您要修改几个字段:')
        number = int(number)
        while number > 0:

            if number==1:
                field = input('请输入您要修改的字段名:')
                field2 = input('请输入修改后的值:')
                a.format(field,field2)
                alter_datas(update_data.format(a, sid))
                break
            else:
                list2.append(',{}={}')
                field = input('请输入您要修改的字段名:')
                list1.append(field)
                field2 = input('请输入修改后的值:')
                list1.append(field2)









                number -= 1


        alter_datas(update_data.format(a,sid))



# main()


a = '{}={}'
a +=',{}={}'
list1 = ['name','张三','age',18]
# print(a)
# print(type(a))
# a.format('name','张三','age',18)
# print(a)
i = 0
while i in range(len(list1)):
    a = a.format(i)
    i += 1
#
print(a)
# b = '{}={},{}={}'
# b = b.format('name','张三','age',18)
# print(b)
# c='{}={}'
# c.format('name','张三')
# print(c)
#
# a1 = '{}'
# a1.format('陈')
# print(a1)

# cursor.execute('use pythonclass')
# # cursor.execute('update dahua set {}={} where s_id={}'.format('s_name','张三',1))
# cursor.execute('update dahua set `s_name`="陈" where s_id={}'.format(1))


























cursor.close()
conn.commit()
conn.close()
